using System;
using System.Data;
using System.Collections;
using CE.Database;
using BKI;
/// <summary>
/// Summary description for BKIDetailDB
/// </summary>
/// 
namespace CE.CarInsurance
{
    public class BKIDetailDB
    {
        public BKIDetailDB()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        public Int64 Insert(BKICarInsurance bkicarInsurance)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "[sprocCarInsuranceInsertUpdateSingleItemBKI]";
            
            SortedList _sPara = new SortedList();
            
            #region Parameter
            _sPara.Add("@CECarId", bkicarInsurance.CECarId);
            _sPara.Add("@RefCEUserId", bkicarInsurance.RefCEUserId);
            _sPara.Add("@PackName", bkicarInsurance.PackName);
            _sPara.Add("@EffDate", bkicarInsurance.EffDate);
            _sPara.Add("@SumIns", bkicarInsurance.SumIns);
            _sPara.Add("@Risk", bkicarInsurance.Risk);
            _sPara.Add("@TpbiPer", bkicarInsurance.TpbiPer);
            _sPara.Add("@TpbiAcc", bkicarInsurance.TpbiAcc);
            _sPara.Add("@Tppd", bkicarInsurance.Tppd);
            _sPara.Add("@C123Driver", bkicarInsurance.C123Driver);
            _sPara.Add("@C123Pass", bkicarInsurance.C123Pass);
            _sPara.Add("@C123Seat", bkicarInsurance.C123Seat);
            _sPara.Add("@Medical", bkicarInsurance.Medical);
            _sPara.Add("@BailBond", bkicarInsurance.BailBond);
            _sPara.Add("@DiscDeduct", bkicarInsurance.DiscDeduct);
            _sPara.Add("@DiscDeductLib", bkicarInsurance.DiscDeductLib);
            _sPara.Add("@DiscNCB", bkicarInsurance.DiscNCB);
            _sPara.Add("@GrossPremVol", bkicarInsurance.GrossPremVol);
            _sPara.Add("@TotalPremVol", bkicarInsurance.TotalPremVol);
            _sPara.Add("@GrossPremComp", bkicarInsurance.GrossPremComp);
            _sPara.Add("@TotalPremComp", bkicarInsurance.TotalPremComp);
            _sPara.Add("@Status", bkicarInsurance.Status);
            _sPara.Add("@Remark", bkicarInsurance.Remark);
            _sPara.Add("@PackNo", bkicarInsurance.PackNo);
            _sPara.Add("@PackageCode", bkicarInsurance.PackageCode);
            _sPara.Add("@PackageSeq", bkicarInsurance.PackageSeq);
            _sPara.Add("@PlanSeq", bkicarInsurance.PlanSeq);
            _sPara.Add("@SubPlan", bkicarInsurance.SubPlan);
            _sPara.Add("@BKICarCode", bkicarInsurance.BKICarCode);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);

            return _result;
        }

        public bool Update()
        {
            return true;
        }

        public bool Delete(Int64 CarId, Int64 UserId)
        {
            MyDatabase _db = new MyDatabase();
            string strSQL = "DELETE FROM tblCarInsurance_BKI WHERE CECarId = @CECarId AND RefCEUserID = @RefCEUserId";
            SortedList _sPara = new SortedList();

            #region Parameter
            _sPara.Add("@CECarId", CarId);
            _sPara.Add("@RefCEUserId", UserId);
            #endregion

            int _result = _db.ExecuteNonSqlStatement(strSQL, _sPara);
            return _result > 0;
        }

        public BKIDetail GetBKIDetail(Int64 CarId,  Int64 UserId)
        {
            BKIDetail _bkidetail = null;
            MyDatabase _db = new MyDatabase();
            //Please Check & update
            string _spName = "sprocBKIGetCarModel";

            SortedList _spara = new SortedList();
            _spara.Add("@CECarID", CarId);
            _spara.Add("@CEUserID", UserId);

            using (DataTable _dt = _db.GetDataTableBySP(_spName, _spara))
            {
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    _bkidetail = new BKIDetail();
                    _bkidetail = FillDataRecord(_dt);
                }
            }
            return _bkidetail;
        }

        
        private BKIDetail FillDataRecord(DataTable myDataTable)
        {
            BKIDetail bkidetail = new BKIDetail();
            //bkidetail.UserId = myDataTable.Rows[0]["UserId"].ToString();
            bkidetail.EffDate = myDataTable.Rows[0]["EffDate"].ToString();
            bkidetail.Risk  = myDataTable.Rows[0]["Risk"].ToString();
            bkidetail.Garage = myDataTable.Rows[0]["Garage"].ToString();
            bkidetail.CarType = myDataTable.Rows[0]["CarType"].ToString();
            bkidetail.CarUse = myDataTable.Rows[0]["CarUse"].ToString();
            bkidetail.Make = myDataTable.Rows[0]["Make"].ToString();
            bkidetail.Model = myDataTable.Rows[0]["Model"].ToString();
            bkidetail.CarYear = myDataTable.Rows[0]["CarYear"].ToString();
            bkidetail.NCB = Convert.ToDouble(myDataTable.Rows[0]["NCB"].ToString());
            bkidetail.Deduct = Convert.ToDouble(myDataTable.Rows[0]["Deduct"].ToString());
            bkidetail.DeductLib = Convert.ToDouble(myDataTable.Rows[0]["DeductLib"].ToString());
            bkidetail.DrvFlag = myDataTable.Rows[0]["DrvFlag"].ToString();
            bkidetail.DrvYear = myDataTable.Rows[0]["DrvYear"].ToString();
            bkidetail.CompReq = myDataTable.Rows[0]["CompReq"].ToString();
            return bkidetail;
        }

        //Additional Response
        public Int64 InsertBKIResponse(string refno, string status, string cxrefno)
        {
            CE.Database.MyDatabase _db = new CE.Database.MyDatabase();
            Int64 _result = -1;
            string _spName = "[sprocTransferDataResponseBKI]";

            SortedList _sPara = new SortedList();

            #region Parameter
            _sPara.Add("@RefNo", refno);
            _sPara.Add("@Status", status);
            _sPara.Add("@CXRefNo", cxrefno);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);

            return _result;

        }
        //for test only
        public void BKICalculation(Int64 CarId , Int64 UserId)
        {
            BKIDetail bkidetail = new BKIDetail();
            BKIDetailDB bkidetaildb = new BKIDetailDB();
            
            //Int64 _cecarid = 1999;
            //Int64 _refceuserid = 100;

            Int64 _cecarid = CarId;
            Int64 _refceuserid = UserId;

            bkidetail = bkidetaildb.GetBKIDetail(_cecarid, _refceuserid);

            string user_id = CE.Configuration.MyConfigurationManager.GetAppSettingValue("TH.BKIUserID");
            
            string eff_date = bkidetail.EffDate;
            string risk = bkidetail.Risk;
            string garage = bkidetail.Garage;
            string car_type = bkidetail.CarType ;
            string car_use = bkidetail.CarUse;
            string make = bkidetail.Make;
            string model = bkidetail.Model;

            string car_year = bkidetail.CarYear;
            double ncb = bkidetail.NCB;
            double deduct = bkidetail.Deduct;
            double deduct_lib = bkidetail.DeductLib;
            string drv_flag = bkidetail.DrvFlag;
            string drv_year = bkidetail.DrvYear;
            string comp_req = bkidetail.CompReq;


            //string eff_date = "4/4/2011";
            //string risk = "";
            //string garage = "";
            //string car_type = "1";
            //string car_use = "1";
            //string make = "Toyota";
            //string model = "TO311-01";

            //string car_year = "2011";
            //double ncb = Convert.ToDouble(0);
            //double deduct = Convert.ToDouble(0);
            //double deduct_lib = Convert.ToDouble(0);
            //string drv_flag = "N";
            //string drv_year = "";
            //string comp_req = "Y";


            //Web service - BKI
            PremiumProcessService a = new PremiumProcessService();
            PremiumBean[] b = a.calculator(user_id, eff_date, risk, garage, car_type, car_use, make, model, car_year, ncb, deduct, deduct_lib, drv_flag, drv_year, comp_req);

            bkidetaildb.Delete(_cecarid, _refceuserid);
            for (int i = 0; i <= b.Length - 1; i++)
            {
                if (Convert.ToDouble(b[i].total_prem_vol) > 0)
                {
                    BKICarInsurance bkicarinsurance = new BKICarInsurance();
                    bkicarinsurance.CECarId = _cecarid;
                    bkicarinsurance.RefCEUserId = _refceuserid;
                    bkicarinsurance.PackName = Convert.ToString(b[i].packname.ToString());
                    bkicarinsurance.EffDate = Convert.ToString(b[i].eff_date.ToString());
                    bkicarinsurance.SumIns = Convert.ToDouble(b[i].sum_ins.ToString());
                    bkicarinsurance.Risk = Convert.ToString(b[i].risk.ToString());
                    bkicarinsurance.TpbiPer = Convert.ToDouble(b[i].tpbi_per.ToString());
                    bkicarinsurance.TpbiAcc = Convert.ToDouble(b[i].tpbi_acc.ToString());
                    bkicarinsurance.Tppd = Convert.ToDouble(b[i].tppd.ToString());
                    bkicarinsurance.C123Driver = Convert.ToDouble(b[i].c123_driver.ToString());
                    bkicarinsurance.C123Pass = Convert.ToDouble(b[i].c123_pass.ToString());
                    bkicarinsurance.C123Seat = Convert.ToDouble(b[i].c123_seat.ToString());
                    bkicarinsurance.Medical = Convert.ToDouble(b[i].medical.ToString());
                    bkicarinsurance.BailBond = Convert.ToDouble(b[i].bailbond.ToString());
                    bkicarinsurance.DiscDeduct = Convert.ToDouble(b[i].disc_deduct.ToString());
                    bkicarinsurance.DiscDeductLib = Convert.ToDouble(b[i].disc_deduct_lib.ToString());
                    bkicarinsurance.DiscNCB = Convert.ToDouble(b[i].disc_ncb.ToString());
                    bkicarinsurance.GrossPremVol = Convert.ToDouble(b[i].gross_prem_vol.ToString());
                    bkicarinsurance.TotalPremVol = Convert.ToDouble(b[i].total_prem_vol.ToString());
                    bkicarinsurance.GrossPremComp = Convert.ToDouble(b[i].gross_prem_comp.ToString());
                    bkicarinsurance.TotalPremComp = Convert.ToDouble(b[i].total_prem_comp.ToString());
                    bkicarinsurance.Status = ""; //Convert.ToString(b[i].status);
                    bkicarinsurance.Remark = ""; //Convert.ToString(b[i].remark);
                    bkicarinsurance.PackNo = Convert.ToString(b[i].pack_no.ToString());
                    bkicarinsurance.PackageCode = Convert.ToString(b[i].package_code.ToString());
                    bkicarinsurance.PackageSeq = Convert.ToString(b[i].package_seq.ToString());
                    bkicarinsurance.PlanSeq = Convert.ToString(b[i].plan_seq.ToString());
                    bkicarinsurance.SubPlan = Convert.ToString(b[i].sub_plan.ToString());
                    bkicarinsurance.BKICarCode = model;
                    bkidetaildb.Insert(bkicarinsurance);

                }
            }
        }
    }
}